How to pull/grab from a chart - Ranged If funion?

[Caisson]

Normally I just do a really long IF or IFOR function. But this doesn't always work because there is too much information.

How do I pull from a chart:

____A___B __C___D

1___M1_____M1__5
2___M1_____M2__4
3___M3_____M3__7
4___M2_____M4__6
5___M3_____M5__8


So in this simplified case, I need to find the value for cell B1 from the cell A1 - the answer is 5 (shown in D1).

How do I tell cell B1 to match the cell A1 with a range of C1:C5. Then the answer would be the corresponding cell D.

An if function would look like this for cell B1:
=IF(A1="M1", 5)


I hope this makes sense. The goal here would be to eliminate really long if functions and rather plug in a chart on the side of my work-page. I can then use the chart to pull information from.

[Pete_UK]

You can use this in B1:

=IFERROR(VLOOKUP(A1,C$1:D$5,2,0),"")

then copy down as far as you need to.

Hope this helps.

Pete

[JohnTopley]

Assuming data in column A to C

in D1

=INDEX($C$1:$C$5,MATCH($A1,$B$1:$B$5,0))

Copy down

[Caisson]

Thanks! The VLOOKUP function was exactly what I needed.

Just out of curiosity, what is the point in the iferror?

[Pete_UK]

Glad to hear it.

If any of the cells in column A contain a value that is not in the table (or empty) then the formula would return an error message - the IFERROR part traps that and returns a blank instead. See what happens if you put XYZ in cell A1.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Caisson]

Glad to hear it.

If any of the cells in column A contain a value that is not in the table (or empty) then the formula would return an error message - the IFERROR part traps that and returns a blank instead. See what happens if you put XYZ in cell A1.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thank you sir. Very helpful. Time for me to get back to work!