Thread: Nesting multiple Vlookup conditions in an If funciton

I am not sure if the VLookup or the If function will be the best way to perform this tast but I think it is they way I would want to go.

I have attached a sample workbook of what I am working with.
Please refer to it.

On sheet 1 I named the table array Ear_Tag.

This is information that is provided to me from the farmers.

Note that there are sometimes more then 1 pig with the same tag number. The only way to tell them apart is by the Test Date.

On Sheet 2
Is where I record the test results.

I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.

If you can help solve this problem for me I would be ever so thankful.
Thanks
Amy

Try this formula in E2:

=INDEX('Pig Inventory'!C$2:C$8,MATCH(1,INDEX(('Pig Inventory'!$A$2:$A$8=$B2)*('Pig Inventory'!$B$2:$B$8=$D2),0),0))

adjust ranges to suit your database, and copy down and across.

Have a look at the INDEX() and MATCH() functions in Excel help for specifics on each function...

The combination here, allows you to index a column and based on vertical positioning (found with the Match() function), we can extract the appropriate value from the indexed column at the right point.

This internal INDEX function:

INDEX(('Pig Inventory'!$A$2:$A$8=$B2)*('Pig Inventory'!$B$2:$B$8=$D2),0)

looks at creating an array of 1's and 0's by checking each of the 2 conditions at each row and returning a 1 if both conditions match, or 0 otherwise...the 1 or 0 is achieved when multiplying results of TRUE or FALSE together.. this coerces TRUE to be a 1 and FALSE to be a 0... so only way to get a 1 final result is multiplying TRUE by TRUE... which is when both conditions match...

Then the MATCH(1,INDEX(....),0) part finds where that 1 is found within that resultant array of 1's and 0's... and returns the position number....

that positions number is then fed to the original INDEX('Pig Inventory'!C$2:C$8.... part and so extract the value from that range at that returned position...

... hope that explains it well enough...

If you go to Tools|Formula Auditing|Evaluate Formula... and keep clicking Evaluate, you will see the formula in action...

Great,

Don't forget to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save