Adapt formula to search multiple columns on different sheets

**GPetcu** · 08-30-2017, 09:56 AM

Hello everyone

Simple one. I have this formula (=IFERROR(IFERROR(INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"") ) that I have been using successfully for some time now.

My question is, how can I adapt the formula instead of searching for the value in C2 in Sheet3!B:B to search a different workbook. All i need is to adapt the formula to search 2 different columns on 2 different sheets (for ex: Sheet1 columns B and C and sheet2 columns E and F).

=IFERROR(IFERROR(INDEX('[Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"") - this is as far as I could get but I don't know how to add another sheet of the same document after this [Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471

I hope it makes sens.

Cheers

**Special-K** · 08-30-2017, 10:46 AM

This doesnt make sense.
You say
" instead of searching for the value in C2 in Sheet3!B:B"
but in the new formula afterwards you have

=IFERROR(IFERROR(INDEX('[Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"")

which clearly still includes a search of Sheet3 column B

So what are you trying to do?
Add 4 searches of the Car Parking workbook for columns Sheet1!B and C and Sheet2!E and F to the existing formula or replace the existing 2 searches with the 4 searches from the car parking workbook?

**GPetcu** · 08-30-2017, 11:08 AM

Hello

I need the formula to search the database attached, search under all columns that have registration no. in them, and return all the details that we have under the searched reg.

I have attached an example of the database to get an idea on what I am trying to achieve.

All i want, in a nutshell, is to adapt the formula to search the database document instead of sheet3 of the registration search.