Lookup at multiple sheets

[nburaq]

Hi Gents,
I am trying to find the values matched in sheet2 and sheet3 for sheet1. if there is no match for sheet 1 the value will be N/A. I placed some values in sheet1 as samples. Itried to solve this with IF-XLOOKUP but it became very complicated. Thanks for your help and comments!

Book1
	A	B
1	No	Value
2	A123	10
3	A124
4	A125
5	A126
6	A127
7	A128
8	B225
9	B226
10	B227
11	C185
12	C186
13	C187
14	D111	N/A
Sheet1

Book1
	A	B
1	No	Price
2	A123	10
3	A124	15
4	A125	12
5	D111
6	D112	8
7	C185	18
Sheet2

Book1
	A	B
1	No	Price
2	A127	4
3	A128	16
4	C186	22
5	C187	8
Sheet3

 

[KRice]

You could do something like this:

Book1
	A	B
1	No	Value
2	A123	10
3	A124	15
4	A125	12
5	A126	N/A
6	A127	4
7	A128	16
8	B225	N/A
9	B226	N/A
10	B227	N/A
11	C185	18
12	C186	22
13	C187	8
14	D111	0
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,XLOOKUP(A2,Sheet3!$A$2:$A$100,Sheet3!$B$2:$B$100,"N/A"))

 

[nburaq]

You could do something like this:

Book1
	A	B
1	No	Value
2	A123	10
3	A124	15
4	A125	12
5	A126	N/A
6	A127	4
7	A128	16
8	B225	N/A
9	B226	N/A
10	B227	N/A
11	C185	18
12	C186	22
13	C187	8
14	D111	0
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,XLOOKUP(A2,Sheet3!$A$2:$A$100,Sheet3!$B$2:$B$100,"N/A"))

Yes, could be... but i just gave this as an example actual sheets are too many rows and i didnt want to change it. Of course, If I cant find any other solution I will use the way you solved. Thanks again

 

[KRice]

I don't understand. My example set the ranges for matching as $A$2:$A$100 and for returning values as $B$2:$B$100. If your actual sheets have a much larger range, change these to cover the entire range. For example, if your sheets have 10,000 rows to search, beginning in row 10, then use $A$10:$A$11000 and $B$10:$B$11000 (note that I expanded the range by 1000 to account for some additional rows should the actual data ranges become somewhat longer).

 

[nburaq]

I don't understand. My example set the ranges for matching as $A$2:$A$100 and for returning values as $B$2:$B$100. If your actual sheets have a much larger range, change these to cover the entire range. For example, if your sheets have 10,000 rows to search, beginning in row 10, then use $A$10:$A$11000 and $B$10:$B$11000 (note that I expanded the range by 1000 to account for some additional rows should the actual data ranges become somewhat longer).

Sorry my bad you are right. Thanks again for the help

 

[KRice]

That's good to hear! I'm happy to help.