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[TABLE="width: 467"]
<tbody>[TR]
[TD]Item name
[/TD]
[TD]Warehouse A
[/TD]
[TD]Warehouse B
[/TD]
[TD]Warehouse C
[/TD]
[TD]Warehouse D
[/TD]
[/TR]
[TR]
[TD]GL43
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD]GL44
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD]GL73
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]250
[/TD]
[/TR]
[TR]
[TD]GL74
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]75
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="width: 226"]
<tbody>[TR]
[TD]Warehouse
[/TD]
[TD]Item name
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]GL43
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]GL44
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]GL73
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]GL74
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]GL43
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]GL44
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]GL73
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]GL74
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]GL43
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]GL44
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]GL73
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]GL74
[/TD]
[TD="align: right"]75
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]GL43
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]GL44
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]GL73
[/TD]
[TD="align: right"]250
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]GL74
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
</tbody>[/TABLE]



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!!!.