Looking for best look up formula for this scenario

[Zedrick13]

Hello everyone,

This might seem a simple scenario, but I've been trying to figure this out working on index match formulas but I am unable to find the right one. Hope you guys can help me, I've included a screenshot of the spreadsheet I'm working on.

So, we have columns 9, 10 and 11. I've added a dropdown for column 9 with the list of different vendors. What I would like to happen is, when I populate column 9, the columns 10 and 11, with the help of a formula, automatically lookup the values I need. For example, I have selected "Carrier Air" in column 9 and I am expecting that columns 10 and 11 would reflect $42,993.34 and 246.34 respectively.

It gets complicated I guess because:
1. The columns with the vendor names are merged
2. The ranges needed for the formula are non-contiguous

Thanks in advance everyone.

Regards,
Zed

 

[rondeondo]

Hi Zed
the screen shot didn't make it into your post. With that it may be clearer what you're trying to do and where the numbers you quote are coming from.

 

[Zedrick13]

Sorry about that. Here's the screenshot @rondeondo .

 

[rondeondo]

Hi Zed
So you're looking for the supplier entered in column 9 amongst the headers in the first row, then you want to return the two values in the row you're in from the relevant columns.
so you want to do =match(supplier in col 9,range of headings,0) this will give you a column number out of the range.
then you want to wrap that result with index()
So in j3 you'll get

=index(c3:h3,match(i9,c1:h1,0))

and in k3 you want almost the same but you want one cell to the right.
k3 :

 =index(c3:h3,match(i9,c1:h1,0)+1)

note: there's an xl2bb plugin available that allows you to paste in a section of sheet that allows others to take a coy quickly of your data layout.

 

[Zedrick13]

thanks a lot @rondeondo . i really appreciate it. enjoy your day!