help with vlookup
 

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

I use this formula (courtesy of Sandyman) to make my checking off easier, as
i just enter the code in and voila!
=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

My question is this: is there a way to extract the cell reference aswell and
have it appear with the other information, incase the price in the stocklist
is incorrect and i need to change it, to save me looking through the 8000 odd
entries...?

Any help much appreciated.

help with vlookup
 

you could use the match function to identify which item in the list matched
your criteria.

=Match($A2, $A$3:$A$10000, 0)

you could incorporate this into the address function to actually get a cell
address

=Address(Match($A2, $A$3:$A$10000, 0) + Row($A$3) - 1 , Column($A$3), 4)



"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

I use this formula (courtesy of Sandyman) to make my checking off easier, as
i just enter the code in and voila!
=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

My question is this: is there a way to extract the cell reference aswell and
have it appear with the other information, incase the price in the stocklist
is incorrect and i need to change it, to save me looking through the 8000 odd
entries...?

Any help much appreciated.

help with vlookup
 

Thank you JMB, that worked perfectly!

"JMB" wrote:

you could use the match function to identify which item in the list matched
your criteria.

=Match($A2, $A$3:$A$10000, 0)

you could incorporate this into the address function to actually get a cell
address

=Address(Match($A2, $A$3:$A$10000, 0) + Row($A$3) - 1 , Column($A$3), 4)



"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

I use this formula (courtesy of Sandyman) to make my checking off easier, as
i just enter the code in and voila!
=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

My question is this: is there a way to extract the cell reference aswell and
have it appear with the other information, incase the price in the stocklist
is incorrect and i need to change it, to save me looking through the 8000 odd
entries...?

Any help much appreciated.

help with vlookup
 

you're welcome - thanks for the feedback!

"BB" wrote:

Thank you JMB, that worked perfectly!

"JMB" wrote:

you could use the match function to identify which item in the list matched
your criteria.

=Match($A2, $A$3:$A$10000, 0)

you could incorporate this into the address function to actually get a cell
address

=Address(Match($A2, $A$3:$A$10000, 0) + Row($A$3) - 1 , Column($A$3), 4)



"BB" wrote:

Hello

I have a spreadsheet of a rather large stocklist. I use this to check off
the price charged on the invoice when we receive the goods, against the
quoted buy price in the spreadsheet. Column A3 - A8000 (ish) contains a
product code; B3 = description; C3 = buy price; D3 = sell price; E3 = Profit
margin, etc.

I use this formula (courtesy of Sandyman) to make my checking off easier, as
i just enter the code in and voila!
=IF($A$2="","",VLOOKUP($A2,$A3:$E10000,COLUMNS($A$ 2:B2),FALSE))

My question is this: is there a way to extract the cell reference aswell and
have it appear with the other information, incase the price in the stocklist
is incorrect and i need to change it, to save me looking through the 8000 odd
entries...?

Any help much appreciated.