Excel: Lookup a corresponding value, multiple conditions

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

impelus

Thread Starter
Joined
Nov 28, 2005
Messages
53
Hi all, sorry for the vague title, but not really sure how to describe my problem other than to show it.

Basically I am trying to do a VLOOKUP/HLOOKUP where one 1 value is exact and 1 value is approximate. I have tried the INDEX(MATCH method, but it doesn't work either since they need to be exact.

I have publish the problem on google docs @ https://spreadsheets.google.com/ccc?key=tweLkcs0iso9ZF85qEexO4Q&hl=en#gid=0
You will see lookup table on the left and then the right is a list of data that needs to find the answer to. I filled in the answers, just so you would see what I the correct answer should be. I absolutely need the lookup portion as I have way more than A and B.

I am using Excel 2007. I would prefer a code I can write in the cell and not a macro/vba.

Thanks in advance,

Jason
 
Joined
Jul 1, 2005
Messages
8,546
Is that GoogleDocs thing supposed to be editable? :confused:

"I would prefer a code I can write in the cell and not a macro/vba."

OK chief. :D

In J3:

=INDEX(INDIRECT("B"&MATCH(G3,A:A,0)+1&":E"&MATCH(G3,A:A,0)+1),1,MATCH(H3,INDIRECT("B"&MATCH(G3,A:A,0)&":E"&MATCH(G3,A:A,0)),1))

:)eek:)
 

impelus

Thread Starter
Joined
Nov 28, 2005
Messages
53
I didn't make it editable since I didn't want someone to change something and then the others not see what I was asking. I made the worksheet editable now (one sheet is read only).

OK. That works. Google Docs gives a wrong answer but excel does it correctly. It gives the answer for the one above.

I replaced the "B" with G3 in your formula to make it dynamic.

Unfortunately I have more than just 4 columns of data, but I can modify the formala to work with it.

Thanks for the help.
 

impelus

Thread Starter
Joined
Nov 28, 2005
Messages
53
Yes it works, I had a space when I copied it. I noticed it and edited my reply.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top