Looking up Table Values that Correspond to other Values

[kmerfeld]

Hello friends,

I am trying to develop a crop rotation for my one acre farm using a spreadsheet, but am running into some trouble. I will describe a simplified version of the problem:

Column A contains a list of numbers say (10, 40, 50, 20)

Column B also contains a list of numbers: (3, 6, 9, 2)

A given row, therefore, contains a set of these: Row 1 contains 10 and 3, Row 2 contains 40 and 6, etc.

In Column C, I want to be able to put in a Column A value and get back the Column B value from the same row.

For example, in column C, I want to type 10 and have excel give me a 3.

It looks something like this:

A B C
1 10 3
2 40 6
3 50 9
4 20 2

When I type this in column C:

A B C

1 10 3
2 40 6
3 50 9
4 20 2 10

I want it to give me this:

A B C

1 10 3
2 40 6
3 50 9
4 20 2 3


I have tried HLOOKUP, but was unsuccessful. And there are too many values in rows B and C to use the IF function.

Do you have any suggestions?

Thanks,

Kevin

[NBVC]

You need Vlookup, not Hlookup

e.g.

In D1, =Vlookup(C1,A:B,2,FALSE) copied down...


Note: You need to type in C and get result in B... if you are looking to overwrite your typed value with new one automatically, you will need VBA....

[kmerfeld]

THANK YOU SO MUCH!

This works great. You have no idea how long I have been trying to figure this thing out.

You made my day.

[NBVC]

Great! You are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[kmerfeld]

Wait,

As I glance at it now, I am not sure it did.

What does the column index number refer to?

[NBVC]

The column number within the lookup table... in this case the lookup table is A:B and the index number is 2.. that is column B...

So Vlookup looks up the value C1 with range A:A and extract the corresponding item in B:B

[kmerfeld]

It works for one of the cells, but for the others it returns a 0.

[NBVC]

Can you attach a small sample sheet showing the problem?

[kmerfeld]

Ok, yep.

Now I have it for sure.

Thanks again.

I'll mark it as solved.


Kevin

[kmerfeld]

Did that go through as solved?