nested VLOOKUP

[EVCOdan]

Hi all,

I'm kind of new to this and trying to write a script that will target data from a different row in the event there is an entry in a specific column of the current row being searched by VLOOKUP

Here is what I'm currently doing, which works if there is no secondary search.


Current script below

=IF(D416="","",VLOOKUP(D416,'Item Browse'!A:AK,37,FALSE))*N416



Script does the following;

If Item# is nothing then nothing is entered
then
If Item# is something then look for Item# in item browse sheet, columns A through AK
then
If found then input column 37 into cell
then
If not found then false
then
Multiply by cell n(column)



Trying to make script do the following;

If Item# is nothing then nothing is entered
then
If Item# is something then look for Item# in item browse sheet, columns A through AK
then
If routing code is something then look for item # in routing code(column) in item browse sheet, columns A through AK
then
If found then input column 37 into cell
then
If not found then false
then
Multiply by cell n(column)

Any help would be greatly appreciated!

 

[Kerryx]

Something like this maybe

Book1
	A	B	C	D	E	F	G	H
1	Item	desc	price	Routing	Col37
2	ab			123	2	1		cd
3	cd			456	3	1		3
4	ef			789	4	1
5	gh			654	5	1
6	ij			987	6	1
7	kl			321	7	1
8	mn			258	8	1
9	op			147	9	1
Item Browse

Cell Formulas
Range	Formula
H3	H3	=IF(H2="","",IFNA(VLOOKUP(H2,$A$1:$E$9,5,FALSE)*F2,IFNA(VLOOKUP(H2,$D$2:$E$9,2,FALSE)*F2,"FALSE")))

=IF(D416="","",IFNA(VLOOKUP(D416,'Item Browse'!A:AK,37,FALSE)*N416,IFNA(VLOOKUP(D416,'Item Browse'!RouteCodeColumn:AK,37-RouteCodeColumn,FALSE)*N416,"FALSE")))

 

[EVCOdan]

Apologies. Have finally gotten a chance to look at this. If i am not mistaken it looks like what you have suggested is trying to search for data in two different locations of the same sheet.

What Im trying to do is search for the item within the sheet and then if there is a value in the routing code column, to bring the number from that routing code column rather then the original column, but only in the event that there is data in the routing code column.

 

[Joe4]

I think it would be much clearer if you showed up some sample data and walked us through an actual example using that data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[EVCOdan]

Yep I figured it out. Details below.

Just sample string that I have to update to accommodate but it's doing exactly what I'm looking for.

=IF(A2="","",IFNA(VLOOKUP(B2,A:D,4,FALSE),IFNA(VLOOKUP(A2,A:D,4,FALSE),"FALSE")))