# INDEX, MATCH, INDIRECT (multiple columns)

1. ## INDEX, MATCH, INDIRECT (multiple columns)

HI all, first post long time creeper.

I am working on a data sheet where the first sheet is a entry template and the other sheets are data tables for the entry template to pull data from

On this entry sheet is BOX1 that is a drop down list with the names of the other sheets/ data tables.
Column 2(sheet1) is a code (references column A of sheet from Box 1)
Column 3(sheet1) is a 2 digit code (references column B of sheet from Box 1)
Column 4(sheet1) is a price that is populated by the criteria entered from Column 2 and 3

Column 4(sheet1) should grab a price from column 3(tablesheet) if there is only data in Column 2(sheet1) . It should grab a price from Column 4(tablesheet) is there is data in Column2(sheet1) and Column3(sheet1).

Example: I want to search the price of (59020)(00) and it returns 100
Now I want to search (59020)(10) and it returns 120.

I have had success running this formula to return data correctly for one column, but cannot get it to search for the additional column 2.

Here is the formula : =INDEX(INDIRECT("'"&\$B\$4&"'!C:C"),MATCH(C5&D5,(INDIRECT("'"&\$B\$4&"'!A:A")=C5)*(INDIRECT("'"&\$B\$4&"'!B:B")=D5),0))

Also the working formula that only uses one column of data currently looks like
=INDEX(INDIRECT("'"&\$B\$4&"'!C:C"),MATCH(C6,INDIRECT("'"&\$B\$4&"'!A:A"),0))

2. ## Re: INDEX, MATCH, INDIRECT (multiple columns)

Try this, entered as an ARRAYED function
Formula:
`Please Login or Register  to view this content.`

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1