Using the LOOKUPs

[JonS]

On a sheet S1, I have a table (T1) with a column of references (C10, c34, etc) . On a second sheet (S2) I have a similar set of references but with more entries than S1.

          Sheet                                  Sheet S2
     Col A     Col:B                 Col:  F        Col:  G

Row 1 c55 £30 C10 £87
Row 2 c103 £40 c55 £30
Row 3 c87 £100 c103 £40
Row 4 C90 £0 c87 £100
Row 5 C10 £87 c90 £0
Row 6 C25

I am trying to search on col A for the same value in col F and put the value of col G in col B. Black fonts are the givens, red cols are the results.

I have puzzled over LOOKUP, Hlookup and VLOOKUP without any success. Caan anyone help, please?

[JonS]

Sorry - my table has not come out right. Will try again tomorrow.

[Rugslug]

Hi Jon,

You will want to use VLOOKUP for the list organized in columns. You can look at it as though your look up values are organized vertically.

=VLOOKUP(A15,$A$1:$B$10,2,0)

Create a look up list in the A1 to B10 range. Col A will have values like C5, C103, C55 while Col B contains the cost values. This can be done on separate sheets, but I’m keeping it simple for now so you don’t have to flip between sheets to see how it works. Your array for separate sheets would be similar to “Sheet1.$A$1:Sheet1.$B$10”.

Now starting in Cell A15 you will type in your Cvalue (C5 etc) that will be used as your entry. Place the above formula in B15. The $A$1:$B$10 portion of the formula (array) will force an absolute range as the formula is copied down the lookup will always look at A1:B10.

The ‘2’ value is the cell value required to “extract”. It must be within the array of columns. Col A is value 1, Col B is 2.

The ‘0’ value tells the array if it is sorted. The lookup list is generally sorted so you can find values in the future when adding to your list. If it is not sorted, but you specify a ‘1’ indicating it is sorted, you will get strange (but computer logical) results.

Generally, have unique values in your lookup list but if duplicate values are present, only the first one it finds will be used.

Tim