Different formulas in one cell.

[itsgrady]

Should be simple but I am having a difficult time putting two lookups in one cell to work in finding the information. If the first range does not have the information then the second range should have it. I will also need to add another range at a later date. Below are the two formulas I would like to add together in one cell:

IF(C25=“”,””,VLOOKUP(C25,’Sheet1’!$A:$G,2,False))

Or

IF(C25=“”,””,VLOOKUP(C25,’Sheet2’!$C:$D,2,False))

Thanks for the help.

 

[etaf]

try using IFERROR
IF(C25=“”,””,IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False), 2nd lookup)

=IF(C2="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False),VLOOKUP(C25,’Sheet2’!$C:$D,2,False) ))

you can add another IFERROR for 3rd lookup

=IF(C2="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False), IFERROR ( VLOOKUP(C25,’Sheet2’!$E:$F,2,False) ) ))

 

[itsgrady]

try using IFERROR
IF(C25=“”,””,IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False), 2nd lookup)

=IF(C2="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False),VLOOKUP(C25,’Sheet2’!$C:$D,2,False) ))

you can add another IFERROR for 3rd lookup

=IF(C2="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False), IFERROR ( VLOOKUP(C25,’Sheet2’!$E:$F,2,False) ) ))

The formula worked well for the first two lookups, but I was not able to do a 3rd lookup.

=IF(C25="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False),VLOOKUP(C25,’Sheet2’!$C:$D,2,False))))

But when I tried to add the third with another IFERROR - it did not work. He is the 3rd formula:

C2="","",IFERROR(VLOOKUP(C25,’Sheet3’!C$24:D$36,2,False)

 

[etaf]

this works without the sheets as a quick test

=IF(C25="","",IFERROR(VLOOKUP(C25,'sheet1'!$A:$G,2,FALSE),IFERROR(VLOOKUP(C25,'sheet2'!$C:$D,2,FALSE),IFERROR(VLOOKUP(C25,'Sheet3'!C$24:D$36,2,FALSE),"X"))))

Mocked up here , in 1 sheet , just to show it work

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	`		REGULAR
2	A0	B0	C0	D0	E0	F0	G0	H0					3rd-1
3	A1	B1	C1	Do	E1	F1	G1	H1
4	A2	B2	C2	Do	E2	F2	G2	H2
5	A3	B3	C3	Do	E3	F3	G3	H3
6	A4	B4	C4	Do	E4	F4	G4	H4
7	A5	B5	C5	Do	E5	F5	G5	H5
8	A6	B6	C6	Do	E6	F6	G6	H6
9	A7	B7	C7	Do	E7	F7	G7	H7
10	A8	B8	C8	Do	E8	F8	G8	H8
11	A9	B9	C9	Do	E9	F9	G9	H9
12	A10	B10	C10	Do	E10	F10	G10	H10
13	A11	B11	C11	Do	E11	F11	G11	H11
14
15
16
17
18
19
20
21
22
23
24
25			1234	3rd-1												1234
26			1235	3rd-2
27			1236	3rd-3
28			1237	3rd-4
29			1238	3rd-5
30			1239	3rd-6
Sheet1

Cell Formulas
Range	Formula
M2	M2	=IF(P25="","",IFERROR(VLOOKUP(P25,$A:$G,2,FALSE),IFERROR(VLOOKUP(P25,$C:$D,2,FALSE),IFERROR(VLOOKUP(P25,C$24:D$36,2,FALSE),"X"))))

 

[itsgrady]

The formula worked well for the first two lookups, but I was not able to do a 3rd lookup.

=IF(C25="","",IFERROR(VLOOKUP(C25,’Sheet1’!$A:$G,2,False),VLOOKUP(C25,’Sheet2’!$C:$D,2,False))))

But when I tried to add the third with another IFERROR - it did not work. He is the 3rd formula:

C2="","",IFERROR(VLOOKUP(C25,’Sheet3’!C$24:D$36,2,False)

this works without the sheets as a quick test

=IF(C25="","",IFERROR(VLOOKUP(C25,'sheet1'!$A:$G,2,FALSE),IFERROR(VLOOKUP(C25,'sheet2'!$C:$D,2,FALSE),IFERROR(VLOOKUP(C25,'Sheet3'!C$24:D$36,2,FALSE),"X"))))

Mocked up here , in 1 sheet , just to show it work

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	`		REGULAR
2	A0	B0	C0	D0	E0	F0	G0	H0					3rd-1
3	A1	B1	C1	Do	E1	F1	G1	H1
4	A2	B2	C2	Do	E2	F2	G2	H2
5	A3	B3	C3	Do	E3	F3	G3	H3
6	A4	B4	C4	Do	E4	F4	G4	H4
7	A5	B5	C5	Do	E5	F5	G5	H5
8	A6	B6	C6	Do	E6	F6	G6	H6
9	A7	B7	C7	Do	E7	F7	G7	H7
10	A8	B8	C8	Do	E8	F8	G8	H8
11	A9	B9	C9	Do	E9	F9	G9	H9
12	A10	B10	C10	Do	E10	F10	G10	H10
13	A11	B11	C11	Do	E11	F11	G11	H11
14
15
16
17
18
19
20
21
22
23
24
25			1234	3rd-1												1234
26			1235	3rd-2
27			1236	3rd-3
28			1237	3rd-4
29			1238	3rd-5
30			1239	3rd-6
Sheet1

Cell Formulas
Range	Formula
M2	M2	=IF(P25="","",IFERROR(VLOOKUP(P25,$A:$G,2,FALSE),IFERROR(VLOOKUP(P25,$C:$D,2,FALSE),IFERROR(VLOOKUP(P25,C$24:D$36,2,FALSE),"X"))))

A great solution. Thank you very much for your help.

Now, tell me about the final “X” at the end? That is what I left off when I was trying to complete it on my own.

 

[etaf]

thats was just to show the final IFERROR , rather then N/A - so if it cannot find the item even in the 3rd vlookup - it will report something , part of my tes
So change that to something meaningful , then you wont get N/A error

so replace the X with - Not Found - or invalid or whatever you want

remove the last iferror

=IF(C25="","",IFERROR(VLOOKUP(C25,'sheet1'!$A:$G,2,FALSE),IFERROR(VLOOKUP(C25,'sheet2'!$C:$D,2,FALSE),VLOOKUP(C25,'Sheet3'!C$24:D$36,2,FALSE) )))

and you will get

N/A of all 3 vlookups can not find C25