Is it possible to do 2 VLOOKUPs?

Question

Hello,
actually, I wrote this:

=IF(ISERROR(VLOOKUP($A5,[childrenname.xls]Sheet1!$B4:$B4,0,FALSE)=$A5),DATE([childrenname.xls]Sheet1!$F4,[childrenname.xls]Sheet1!$G4,[childrenname.xls]Sheet1!$H4),"")

I would like to know if it is possible to put two VLOOKUPs in the IF to verify that the result of the first one is correct...

thank you in advance
boeno

gbinforme · Accepted Answer

Hello  Well, actually I just want to know if we can do two searches in a row  I understand that you absolutely want to use VLOOKUP, even though it apparently doesn't serve any purpose, as I said.  So you can stack several of them, just "concatenate" :  = VLOOKUP($A5;[nomenfants.xls]Sheet1!$B4:$B40;0;FALSE)&VLOOKUP($A5;[nomenfants.xls]Sheet2!$B4:$B40;0;FALSE)&VLOOKUP($A5;[nomenfants.xls]Sheet3!$B4:$B40;0;FALSE)(  it's the character "&" that concatenates the results.  You can also, of course, check the result beforehand: IF(ISERROR(... and let us know the formula you obtained so that I can understand the reason.  Good luck  --  always zen

gbinforme · Answer

Hello

it is indeed possible to use multiple VLOOKUP functions,
but I believe you are not using the correct function.

IF(ISERROR(VLOOKUP($A5;[nomenfants.xls]Sheet1!$B4:$B4;0;FALSE)=$A5)

can be written as: IF([nomenfants.xls]Sheet1!$B4=$A5)
because VLOOKUP is a function that searches within a range,
and you are only using it on a single cell.

did I understand correctly?

--

stay calm

boeno · Answer

Well, actually I would just like to know if we can perform two searches in a row... I'm just missing the syntax. In fact, I would like to take into account the results of both searches to display the final result.

boeno · Answer

In fact, I have a list of names and surnames in one table.
For example, in column A I have the registration numbers, in column B the last names, and in column C the first names.

In another table, I have almost the same information except that I also have the date of birth of each person.

The problem is that in the first table, it is structured like this:

Column A Column B Column C

0001 TOTO Albert
0001 Henry
0002 TATA Jean
0002 Frederic
0002 Magali
0002 Jerome

Actually, I have the name of the child appearing under that of their father (or mother).
So in the example, Henry is the son of Albert TOTO and Jean, Frederic, Magali, and Jerome are the children of Jean TATA.

The second table (where there are the dates of birth) is structured like this:

Column A Column B Column C Column D

0001 TOTO Henry 27/01/1992
0002 TATA Frederic 15/05/1972
0002 TATA Magali 17/10/1979
0002 TATA Jerome 09/02/2000

And I would like to add the dates of birth to the first table so that it becomes:

Column A Column B Column C Column D

0001 TOTO Albert
0001 Henry 27/01/1992
0002 TATA Jean
0002 Frederic 15/05/1972
0002 Magali 17/10/1979
0002 Jerome 09/02/2000

If you have any ideas, since Friday I've been struggling a bit; would it be better to use functions or create a macro... :s

Thank you in advance
Boeno

gbinforme · Answer

Hello  If you could provide us with an example on: https://www.cjoint.com/  and give us the resulting address, it would be easier.   --  always zen

JvDo · Answer

Good evening,

a proposal in cell D2:

=IF(SUMPRODUCT(($H$2:$H$23=A2)*($J$2:$J$23=C2))=1,INDEX($K$2:$K$23,SUMPRODUCT(($H$2:$H$23=A2)*($J$2:$J$23=C2)*(ROW($J$2:$J$23)-1)),"")

the data is in A2:C7, the retrieved dates will be in D2:D7, the "date of birth" base is in H2:K23

best regards

guilders · Answer

Hello,
Is there a formula that allows testing several VLOOKUPs and stopping at the first one that returns a result?
I am searching in a database-format table for a result that depends on several conditions, which I need to check in order of priority. I have a total of 9 conditions, with the 9th always returning a result.
Currently, my formula is doing this: IF(ISNUMBER(VLOOKUP(1stCondition)), 1stCondition; IF(ISNUMBER(VLOOKUP(2ndCondition)), 2ndCondition;....
This works, although it makes for a long formula, but I don't know why beyond the 7th condition, Excel no longer accepts the formula (even though it's not too long).
I could split my long formula into two and then retest in a 3rd column if the 1st formula gives a result; otherwise, take the one from the 2nd column, but that would give me a total of 3 columns instead of 1, and I don't want to deal with 2 hidden columns.
I hope I have been clear; if anyone has a better idea?
Thank you.

gbinforme · Answer

Hello  but I don't know why beyond the 7th condition,  Simply because Excel only accepts 7 levels of functions, at least in the versions up to 2003, as the latest version 2007 accepts 84.  Without using other columns, you can virtually split your formula into two packages, meaning that instead of stacking your conditions linearly, you finish one set of levels and then take another series in the same formula.  Personally, I try to design my workbook to avoid this problem because large formulas weigh down the workbook and significantly reduce its responsiveness due to the heaviness of the calculations.  However, it's not always easy to find the time to simplify...  --   always zen

guilders · Answer

Hello,

Thank you for your response.

Can you give me more information on how to proceed?

Here is currently my formula, divided into 3 columns:

1st column:
=IF(ISNUMBER(VLOOKUP($A$2&D7&"A",output!$H:$N,7,FALSE)),VLOOKUP($A$2&D7&"A",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($A$2&D7&"T",output!$H:$N,7,FALSE)),VLOOKUP($A$2&D7&"T",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($A$2&E7&"A",output!$H:$N,7,FALSE)),VLOOKUP($A$2&E7&"A",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($A$2&E7&"T",output!$H:$N,7,FALSE)),VLOOKUP($A$2&E7&"T",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($D$2&D7&"A",output!$H:$N,7,FALSE)),VLOOKUP($D$2&D7&"A",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($D$2&D7&"T",output!$H:$N,7,FALSE)),VLOOKUP($D$2&D7&"T",output!$H:$N,7,FALSE),))))))

2nd column:
=IF(ISNUMBER(VLOOKUP($D$2&E7&"A",output!$H:$N,7,FALSE)),VLOOKUP($D$2&E7&"A",output!$H:$N,7,FALSE),IF(ISNUMBER(VLOOKUP($D$2&E7&"T",output!$H:$N,7,FALSE)),VLOOKUP($D$2&E7&"T",output!$H:$N,7,FALSE),))

3rd column (which displays the result):
=IF(IF(F7<>0,F7,IF(G7<>0,G7,VLOOKUP("100"&D7&"A",output!$H:$N,7,FALSE)))<1,IF(F7<>0,F7,IF(G7<>0,G7,VLOOKUP("100"&D7&"A",output!$H:$N,7,FALSE)))*VLOOKUP("100"&D7&"A",output!$H:$N,7,FALSE),IF(F7<>0,F7,IF(G7<>0,G7,VLOOKUP("100"&D7&"A",output!$H:$N,7,FALSE))))

Is it possible to do 2 VLOOKUPs?

9 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.