2 variable search criteria lookup on different tabs? Help!

Hello all!

Hoping someone can help, I don’t even know what type of formula can perform what I’m looking for.

In the attached workbook I have two tabs, A and B.

I am trying to fill in the yellow cells on tab B (cells C2:C8) with the appropriate price in tab A. The search parameters in tab B show that I want to search the Zone and the Weight and return the price in the corresponding table on tab A.

Can anyone help with this?

Try

in C2

=INDEX(a!$B$3:$H$12,MATCH($B2,a!$A$3:$A$12,1),MATCH($A2,a!$B$2:$H$2,0))

Hi, to all!

Another option could be:
[C2] : =VLOOKUP(B2,a!A$3:H$12,A2)

And drag it down. Blessings!

Originally Posted by johnmpl

Hi, to all!

Another option could be:
[C2] : =VLOOKUP(B2,a!A$3:H$12,A2)

And drag it down. Blessings!

Thank you so much!!! can you explain the logic in this vlookup? How does the formula know to cross reference the zones and the weights?

thank you!

Originally Posted by amajor99

Thank you so much!!! can you explain the logic in this vlookup? How does the formula know to cross reference the zones and the weights?

Sure!!

VLOOKUP is a function that searches for a value in the first column of a matrix, and returns as a result the same value related according to the column that we want.
In the example that you attached, the first column corresponds to the weights, which are arranged from least to greatest, so the search can be done in an approximate way (without the last argument of the VLOOKUP function, since, by default, the function looks approximately).
As for the zones, these coincide with the number of the column that you want to return from the range A2:H12 -> Zone 2 corresponds to the second column, 5 to the fifth column, and so on.

I hope I have been clear, English is not my native language. Blessings!

too late!!

Originally Posted by johnmpl

Sure!!

VLOOKUP is a function that searches for a value in the first column of a matrix, and returns as a result the same value related according to the column that we want.
In the example that you attached, the first column corresponds to the weights, which are arranged from least to greatest, so the search can be done in an approximate way (without the last argument of the VLOOKUP function, since, by default, the function looks approximately).
As for the zones, these coincide with the number of the column that you want to return from the range A2:H12 -> Zone 2 corresponds to the second column, 5 to the fifth column, and so on.

I hope I have been clear, English is not my native language. Blessings!

That was very clear, thank you so much!!

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Thank you.