How to show price in a separate cell when an item is selected in a drop-down box?

Hello and welcome to the Forum,

What you probably need is a VLOOKUP formula. I don't have a mac but this should work in macs as it does in Win.

abousetta

Guys...

FANTASTIC!

You've made my day Thanks very much! I wasn't expecting an answer this quickly, nor one that just works "out of the box", so to speak.

One further question - I've tried fiddling with it and have only succeeded in breaking it so far:

Is there a parameter in the VLOOKUP formula (e.g.=IFERROR(VLOOKUP(C6,'Home Prices'!B87:C106,2,0),"")) which would allow me to pull the data from two cells adjoining the cells that are in the drop-down box?

(e.g. item, cost, notes)

...actually, an extra question if I may:

I've just discovered named ranges - I've tried to replace the table_array section with a named range thusly:

=IFERROR(VLOOKUP(C11,'Home Prices'!B150:C169,2,0),"")

becomes

=IFERROR(VLOOKUP(C11,"namedRange",2,0),"")

...but it doesn't work. What am I doing wrong here?

Originally Posted by Roger Govier

Hi Jim

Remove the quotes around "NamedRange"

=IFERROR(VLOOKUP(C11,namedRange,2,0),"")

If you want data from further values in the table, just increase the offset from 2 to 3 or 4 or wherever the data is in the range relative to the looked up value.

You could use a single formula which changes the offset parameter automatically, if you make use of the COLUMN() function

=COLUMN(B11) will return 2.
As you copy the formula to the right , B11 will cahnge to C11 and return 3 etc (the row number is irrelevant).
So
=IFERROR(VLOOKUP(C11,namedRange,column(B11),0),"")

as you copy across, will pull successive values from your range.

Works like a charm - thankyou very much for taking the time to help, it's greatly appreciated

Hi,

Glad we could be of assistance. If you are satisfied with the results, please mark the thread as [SOLVED] (next to the title on the first post). Also positive recognition (scales) are always welcomed .

If want to mark the post as solved then:

1) Click the Edit button on your first post in the thread
2) Click Go Advanced
3) select [SOLVED] from the Prefix dropdown
4) click Save Changes

Good luck.

abousetta