VLOOKUP (or something else) to find value in column corresponding to changing number

[Harryf]

Hi everybody!

I have a need to look up the cost price of items entered in a spreadsheet. The item code is listed down in column A. To the right of column A (let's say from columns K to T) I have 10 columns for a range of options available for each item listed in column A - and each option has a different price.

So, let's say I have item BB100 in cell A2 (row 1 reserved for headings), I would then have a price in column K2, a price in L2, a price in M2 etc, all the way up to T2. The headers of columns K to T, i.e. cells K1 to T1, are simply the numbers 1 through 10, 1 being used for the colour white, 2 for the colour blue, etc.

If item BB100 is sold, I only need to know the corresponding "value" linked to that colour to find the correct price, so if I then change the item code from BB100 to BB100/1 for white on the spreadsheet where the data of the sold stock is captured (Say Sheet2) , I would like Excel to return the corresponding value in the range K2:T100 from the spreadsheet where the cost price is stored (Say Sheet1). The value for the actual item on Sheet1, column A will still be BB100, though - I only thought of adding the "/1" on Sheet2, to indicate a white item BB100.

Does anyone know of some magic formula/VBA/function which would return to the relevant cell on sheet 2 where the answer needs to be (Let's say cell B2 on Sheet2), the value from Sheet1 under heading 1 (say column K in my example) for item BB100, or from the column linked to the heading 2 if I added "/2" to the BB100?

There are just too many items listed to have a row allocated to each item, say BB100/1, BB100/2 and then having all the values in a single column, hence my hoping that there may be some magic out there...

I was not trying to direct anybody in a specific direction with what I'd described above - I used it more to explain my need.

Hope someone can help me out!

Kindest possible regards

Harry Fröhlich

 

[offthelip]

this should do it:
=INDEX(Sheet1!K2:T2,1,MID(Sheet1!A2,FIND("/",Sheet1!A2)+1,255))

 

[Harryf]

Brilliant! Thanks Offthelip! I just changed the 2 references to Sheet1!A2 to Sheet2!A2 to accommodate my data.

Thank you very much!
h

 

[Harryf]

Hi offthelip!

The formula does not exactly do what I thought it did... The problem that I have is that it always returns the value 1 row down from the reference point in the INDEX function.

This is OK if the item's basic code is 1 row down, but if the item's basic code is 27 rows down from the heading, it still returns the value for the first row below the number following the "/" sign. As you can see, I need your value of 1 to find the actual item code on Sheet1, return the row number, and use this row number in some way to return the cost price of the item 27 rows down from the heading - in my example of 27 above...

Is there a way to find the row value of a cell on Sheet1 using a value found on Sheet2 in a formula also placed on Sheet2? What I mean is, if I have value AA100/1 in cell A7 on sheet 2, is there a way that a formula placed in B7 on Sheet2 can help me find the value AA100 (the code without the / and the colour indicator) on Sheet1 by running down column A - and then return the CELL("row",range on Sheet1) once it finds the value AA100, so that I can use that number in some way as the row number (second criteria of the INDEX function) in your formula?

Should we consider something else?

I find it difficult to express myself, it seems...

Let's hope it makes sense...

 

[offthelip]

From what i understand you wnat to do a vertical lookup with the text before the slash, assuming this is what you want this should do it:
I assume the list of name si in column A of sheet 1


=INDEX(Sheet1!K2:T100,MATCH(LEFT(Sheet2!A2,FIND("/",Sheet2!A2)-1),Sheet1!A1:A100,0),MID(Sheet2!A2,FIND("/",Sheet2!A2)+1,255))

 

[Harryf]

Amazing... This stood up to absolutely every trick that I could throw at it... Thanks offthelip! This is exactly what I needed! And so quickly! Superb...

h

 

[offthelip]

I am pleased to be ableto help