Lookup data in a set of range that change

[feni1388]

Hi All,
I hope someone can help me how to use the vlookup to find data in a set of range that may change.

Sheet 1 is the place where I need to find qty of each item in each warehouse.
Sheet 2 is the data that will be downloaded from database.
I can use vlookup to find the qty of each item in each warehouse, but the problem is that the range will change when there's new items coming in (the row will get longer).

Sheet 1

Item name	Warehouse A	Warehouse B	Warehouse C	Warehouse D
GL43	100	200	50	300
GL44	50	50	100	300
GL73	50	100	50	250
GL74	30	100	75	500

<tbody>
</tbody>

Sheet 2

Warehouse	Item name	Quantity
A	GL43	100
A	GL44	50
A	GL73	50
A	GL74	30
B	GL43	200
B	GL44	50
B	GL73	100
B	GL74	100
C	GL43	50
C	GL44	100
C	GL73	50
C	GL74	75
D	GL43	300
D	GL44	300
D	GL73	250
D	GL74	500

<tbody>
</tbody>

Thanks in advance

 

[baitmaster]

welcome to the board

A couple of things

First, I'm not a fan of VLOOKUP and I suggest you use an alternative which is slightly trickier at first, but less prone to error and more versatile. Use a combination of INDEX and MATCH, as follows:
=INDEX(value range, MATCH(lookup value, lookup range,0))
Where:
- value range = the range of cells containing your values. In your example this is column 3
- lookup value = which ever item you are searching for, e.g. item name. In your example we actually need to combine 2 values, more on this in a minute
- lookup range = the range containing the codes you're trying to match against. Again, in your example this is actually 2 columns, a combination of say A and GL43. In VLOOKUP this can only be column 1, but Index/Match approach gives you versatility, you don't have to look in column 1 and can use any column

Second, since we're trying to search for 2 values combined, we can do this using an Array Formula. It's more complex than a normal formula, and requires you to submit it using Shift + Ctrl + Enter, rather than just Enter

I've copied your example onto one worksheet (columns A:C), and added 2 lookup values, in cells E1 and F1. A suitable formula, submitted as above, is =INDEX(C:C,MATCH(E1&F1,A:A&B:B,0))
You'll know it's an array formula as it will have {curly brackets} around it when you've finished

Hope this helps

 

[feni1388]

Hi Baitmaster,

Thanks.... I tried it already and it worked!!!.