Skip to main content

Conas vlookup a fháil ar an gcéad, an 2ú nó an naoú luach meaitseála in Excel?

Má cheaptar go bhfuil dhá cholún agat le Táirgí agus cainníochtaí mar atá thíos an pictiúr a thaispeántar. Chun cainníochtaí an chéad nó an dara banana a fháil amach go tapa, cad a dhéanfá?

Anseo is féidir leis an bhfeidhm vlookup cabhrú leat déileáil leis an bhfadhb seo. San Airteagal seo, taispeánfaimid duit conas vlookup a fháil ar an gcéad, an dara nó an naoú luach meaitseála leis an bhfeidhm Vlookup in Excel.

Faigh Vlookup an chéad, an 2ú nó an naoú luach meaitseála in Excel leis an bhfoirmle

Go héasca vlookup faigh an chéad luach meaitseála in Excel le Kutools for Excel


Faigh Vlookup an chéad, an 2ú nó an naoú luach meaitseála in Excel

Déan mar a leanas le do thoil chun an chéad, an 2ú nó an naoú luach meaitseála a fháil in Excel.

1. I gcill D1, iontráil na critéir a theastaíonn uait a fheiceáil, cuirim isteach an Bhanana anseo.

2. Gheobhaidh muid anseo an chéad luach meaitseála banana. Roghnaigh cill bhán mar E2, cóipeáil agus greamaigh foirmle =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) isteach sa Bharra Foirmle, agus ansin brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna.

nótaí: San fhoirmle seo, is é $ B $ 2: $ B $ 6 raon na luachanna meaitseála; $ A $ 2: $ A $ 6 an raon leis na critéir uile maidir le vlookup; Is é $ D $ 1 an chill ina bhfuil na critéir shonraithe vlookup.

Ansin gheobhaidh tú an chéad luach meaitseála banana i gcill E2. Leis an bhfoirmle seo, ní féidir leat ach an chéad luach comhfhreagrach a fháil bunaithe ar do chritéir.

Chun aon naoú luachanna coibhneasta a fháil, is féidir leat an fhoirmle seo a leanas a chur i bhfeidhm: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + Shift + Iontráil eochracha le chéile, tabharfaidh an fhoirmle seo an chéad luach comhoiriúnaithe ar ais.

nótaí:

1. Chun an dara luach meaitseála a fháil, athraigh an fhoirmle thuas go =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)), agus ansin brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna. Féach an pictiúr:

2. Ciallaíonn an uimhir dheireanach san fhoirmle thuas an naoú luach meaitseála de na critéir vlookup. Má athraíonn tú é go 3, gheobhaidh sé an tríú luach meaitseála, agus athróidh sé go n, gheofar amach an naoú luach meaitseála.


Faigh Vlookup an chéad luach meaitseála in Excel le Kutools for Excel

Yis féidir le ou an chéad luach meaitseála in Excel a fháil gan cuimhneamh ar fhoirmlí leis an Cuardaigh luach ar an liosta foirmle foirmle de Kutools le haghaidh Excel.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Roghnaigh cill chun an chéad luach meaitseála a aimsiú (deir cill E2), agus ansin cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle. Féach an pictiúr:

3. Sa Cúntóir Foirmle bosca dialóige, cumraigh mar seo a leanas:

  • 3.1 Sa Roghnaigh foirmle bosca, aimsigh agus roghnaigh Cuardaigh luach ar an liosta;
    Leideanna: Is féidir leat an scagairí bosca, cuir focal áirithe isteach sa bhosca téacs chun an fhoirmle a scagadh go tapa.
  • 3.2 Sa Tábla_eagar bosca, roghnaigh an tábla ina bhfuil na chéad luachanna luacha meaitseála.;
  • 3.2 Sa Cuardach_luach bosca, roghnaigh an cill ina bhfuil an critéir tabharfaidh tú an chéad luach ar ais bunaithe ar;
  • 3.3 Sa Colún bosca, sonraigh an colún a bhfillfidh tú an luach comhoiriúnaithe uaidh. Nó is féidir leat uimhir an cholúin a iontráil sa bhosca téacs go díreach mar a theastaíonn uait.
  • 3.4 Cliceáil ar an OK cnaipe. Féach an pictiúr:

Anois déanfar an luach cille comhfhreagrach a dhaonraú go huathoibríoch i gcill C10 bunaithe ar roghnú liosta anuas.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (43)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
You only return first match with VLOOKUP so your article "How To Vlookup Find The First, 2nd Or Nth Match Value In Excel?" does not make any sense....
This comment was minimized by the moderator on the site
Hi Sima,
"VLOOKUP" is a Google search term, so I used it as a keyword for the article title. The main operation of this article is to lookup values. I'm sorry for the misunderstanding.
This comment was minimized by the moderator on the site
What if the next record on col b is a duplicate how do ignore duplicate? Let's Banana has quantities of 200 twice? Can you please help me figure out how to ignore the duplicate?
This comment was minimized by the moderator on the site
Cna anybody Explain the small function with the IF statement for me please? I don't really understand how that array works :(
This comment was minimized by the moderator on the site
The following formulas works perfect for me:
(a) or(b) =IF(H8='Raw Data'!B4,INDEX('Raw Data'!H6:H20,SMALL(IF(C18='Raw Data'!B6:B20,ROW('Raw Data'!B6:B20)-ROW('Raw Data'!B6:B20)+1),1)))

However, I have encountered a problem where there are 7 sets of the same criteria but in different columns:(1) can the above formula (a) be repeated and search in a different cells in a single formula and(2) the above formula (b) only allows up to two sets of the similar search with matched value result but when trying for the third set in the formula as showed below, Microsoft Excel appeared as "You've entered too many arguments for this function."
=<span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">IF(H8='Raw Data'!J4,INDEX('Raw Data'!P6:P20,SMALL(IF(C18='Raw Data'!J6:J20,ROW('Raw Data'!J6:J20)-ROW('Raw Data'!J6:J20)+1),1))),</span>
This comment was minimized by the moderator on the site
What do Ctrl + Shift + Enter do?? At the beginning I didn'
This comment was minimized by the moderator on the site
Hi,An array formula needs to hit the Ctrl + Shift + Enter keys simultaneously to get the correct result.
This comment was minimized by the moderator on the site
I have a scenario... How do I get last price of anything for reference... Example: Banana first price was 200... While purchasing for second time; I need to display 200 in my expected price cell and then if I buy that on the day at 220, I will put this value manually as 220... Whenever next time I will buy banana; I need to display 220 from the last purchase price
This comment was minimized by the moderator on the site
Try =INDEX($B$2:$B$6,XMATCH(TRUE,EXACT($D$1,$A$2:$A$6),0,-1))

This is essentially reversing the search order and returning the first match using the XMATCH function.

Better late than never, hopefully helps someone :)
This comment was minimized by the moderator on the site
Perfect explanation, thanks.
This comment was minimized by the moderator on the site
If the first or any of the other entry's for 'banana' column B was blank cell, of which I don't require this number, what changes are required to this formula to skip blank cell in column B.
This comment was minimized by the moderator on the site
Sorry I am using this formula
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
This comment was minimized by the moderator on the site
SOLVED:
=SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

If 2nd or 3rd number required exchange ),1) to 2 or 3

This formula does not require index, as it directly looks at the value in Cell
This comment was minimized by the moderator on the site
Correction to previous formula:
The value was reading either the lesser or greater.

Updated formula
=INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

This skips blank cell and places value of non blank cell. Replace +1 with +2 or +3 for 2nd or 3rd value
This comment was minimized by the moderator on the site
And if you want the last, second last, nth last just add a counter (count the number of events already hapenned) to the end and subtract it by 0,1,n respectively.

Thank you so much! I was searching for this for a long time
This comment was minimized by the moderator on the site
Good Day,
Sorry can't help you with this yet. Thank you for your comment.
This comment was minimized by the moderator on the site
Is it possible to find an average of the non-unique data. Or would it be possible to have a list dropdown on the cell of the various values?
This comment was minimized by the moderator on the site
Good Day,
Sorry can't help you with this yet. Thank you for your comment.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations