VLOOKUP and INDIRECT

[Umesh lohani]

=IFNA(VLOOKUP($A3, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$A$2:$A$6"), $A3)>0), 0)) & "'!$A$2:$C$6"), 2, FALSE), "Not found")
HI FOLKS,
THIS IS THE FORMULA IN "ITEM COLUMN " AND WHEN I TRIED TO PUT IN MY EXCEL SPREADSHEET IT SHOWS #N/A ERROR OR NOT FOUND ACTUALLY THIS IS NET DOWNLOADED WORKBOOK AND IN THIS WORKBOOK IN ITEM COLUMN CELL B2 SHOWS ORANGES WITH THE HELP OF THIS FORMULA AND I TRIED SO MANY TIMES BUT MY ANSWER IS COMING NOT RELEVANT SO GUYS PLZ HLP ME WITH YOUR EXCEL FORMULA TRICKS I HOPE YOU WILL GIVE BEST SOLUTIONS! THIS WORK HAS FOUR REFERENCE SHEETS "EAST" "SOUTH" "NORTH" "WEST"
WHICH IS,

Lookup sheets

East

South

North

West

"EAST"

Order no.	Item	Amount
114​	Oranges	$127​
105​	Bananas	$100​
106​	Pears	$110​
107​	Lemons	$145​
110​	Apples	$100​

"NORTH"

Order no.	Item	Amount
111​	Oranges	$120​
113​	Bananas	$120​
117​	Pears	$130​
119​	Lemons	$125​
121​	Apples	$140​

"SOUTH"

Order no.	Item	Amount
116​	Oranges	$115​
118​	Bananas	$118​
115​	Apples	$120​
120​	Lemons	$118​
112​	Pears	$130​

"WEST"

Order no.	Item	Amount
101​	Oranges	$125​
102​	Bananas	$120​
104​	Lemons	$132​
108​	Apples	$118​
109​	Pears	$115​

SO PLZ HELP ME AS SOON AS POSSIBLE M EAGER TO KNOW THE ANSWER

REGARDS
UMESH

Order no.	Item	Amount
101​	Oranges	$125​
102​	Bananas	$120​
103​	Not found	Not found
104​	Lemons	$132​
105​	Bananas	$100​
106​	Pears	$110​
107​	Lemons	$145​
108​	Apples	$118​
109​	Pears	$115​
110​	Apples	$100​
111​	Oranges	$120​
112​	Pears	$130​
113​	Bananas	$120​
114​	Oranges	$127​
115​	Apples	$120​
116​	Oranges	$115​
117​	Pears	$130​
118​	Bananas	$118​
119​	Lemons	$125​
120​	Lemons	$118​

 

[Peter_SSs]

Welcome to the MrExcel board!

I have moved your post out of the old thread that you had posted in as this was not closely related to that old thread.

Also, for the future, please review the Forum Rules and in particular #14 in relation to not posting in all upper case