Ask Your Question
0

Using the LOOKUPs [closed]

asked 2013-12-07 01:30:26 +0200

JonS gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 14:30:33.021283

Comments

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

JonS gravatar imageJonS ( 2013-12-07 01:31:38 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2013-12-07 03:38:51 +0200

Rugslug gravatar image

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-12-07 01:30:26 +0200

Seen: 328 times

Last updated: Dec 07 '13