Excel 2007 : Advanced VLOOKUP

[DvDj]

Hi Everyone,

Can anyone advise me on the following.

Take a look at this table of data.
\1

Is it at all possible to select cell data based on a reference input by the user of both an Item (col A) and a quantity (row 1) to locate a price, so if a user input 0219 as the item and in another cell 250, excel would return a value of £4.62? As far as I am aware using VLOOKUP doesn't fulfill my needs.

Many thanks

[NBVC]

Try

=Index(A1:H19,Match(ItemInput,A1:A19,0),Match(QtyInput,A1:H1,0))

Where ItemInput and QtyInput could be cell references where the user inputs those values, respectively.

[DvDj]

Hi NBVC,

Thank you sooo much, it works a treat. Hadn't come across this function before, so your help has taught me something new today!

Many, Many Thanks

[NBVC]

No problem..Glad to have taught you something... always nice to learn.

Can you please mark your thread [Solved] by...

Editing your original Post
Click on Go Advanced
Select [Solved] from the Title dropdown
Click Submit

[NBVC]

From PM:

NBVC,

You kindly resolved a problem I had yesterday through a post entitled 'Advanced VLOOKUP'. Since implementing your solution it got me wondering about whether or not the formula could be adapted to accomodate nested IF statements.

If you remember you showed me how INDEX could be used with MATCH to reference a cells value. You may also recall this was based on an item number and a quantity. The quantities were laid out 25,50,100,250,500,1000 and 5000.

How would i get the formula to say if the quantity is 25 or less use column B if quantity is 50 or less use column C etc. as the column of reference?

Kind regards

Perhaps:

Please Login or Register  to view this content.

I deleted the last 0 in the 2nd Match() function... This makes the Match find the largest value in an ascending order list that is Less than or equal to the lookup value and uses that to get your table result.

[DvDj]

NBVC,

Many thanks again NBVC that resolves the issue and takes the spreadsheet to the next level.

Really appreciate your help