Checking Different {Lookup_Ranges} if Missing from one Lookup Range..!!!

Checking Different {Lookup_Ranges} if Missing from one Lookup Range..!!!

Dear Forum,

I know of getting the adjacent values based on a Lookup Value from a Lookup Range using VLOOKUP,HLOOKUP,INDEX,MATCH Function combinations..
However, We can only provide a single Lookup Range to the Function's Parameters...
In a situation where if the Lokkup_Value does not exist in any particular Range, then we want the Search to be carried from another LookUp_Range how would we do it..

I know we can use several IF's to get the result but it fails if the No of Lookup_Ranges are more than 7 ..
Presently, I have to lookout for Single Alphabets Value given in a Group..

A I J Q Y - 1 , Now as seen this group of Alphabets are given a value of 1 and now if I need to search for the Alphabet A I need to get the answer as 1 , however if the Alphabet would have been any other than the one given in the Gruop then it wil give a #N/A ERROR...But I have another group B K R - 2 so If the Letter is not from the First Group then it should then search in the Second Group and give the answer 2 for the Alphabet K...

This is an Anology, I would also like to check between several Sheets using the same approach if possible..

Please find the attachment..

Warm Regards
e4excel

Let's put this formula in C2, then copy down:

=VLOOKUP("*" & B2 & "*", $I$2:$J$9, 2, 0)

It's at points like these when I stop my clients "creativity" and instruct them to manage the organization of their workbook, do not let the workbook manage you.

Create a single sheet wherein you can store this table properly. Use it from there. Let your workbook grow from a better starting point, don't keep programming around prior chaotic decisions. Fix them.

Dear JB,

Point Noted and totally agree...
However, In certain scenarios if you need to check for a particular client when the Transaction for different clients are entered in Different Sheets and also on different Rows with the only good thing is that the data is in the same Columns as all the Sheets for the Months would have the same structure..

I am actually working on getting the Inputs for Various Clients from different Sheets and this Template was not deisgned by me..

I would have thought of consolidation however till a New System is made it will still be entered in the Seperate Monthly SHeets...and therefore consolidation is also not a good option..

Though what you suggest is of utmost importance more than pondering on retrieving the data in different ways its always good to have a good deisgn..

Warm Regards
e4excel

See if you can apply this 3D VLOOKUP trick...

'3D VLOOKUP (& HYPERLINK)
Here's a page showing a formula method doing a VLOOKUP across multiple worksheets. The formula will return the answer from the first sheet that has the searched value. There's a sample file as well.

3D VLOOKUP() and HYPERLINK()

There's a limit to my personal tolerance for complexity. I find readability a plus.

The INDIRECT() method is already a pain to read and manage, I would resist trying to make the "cell range string" self adjusting, too, the formula could become horrid beyond reason.

The method does require the "cells evaluated" be the same on every sheet. My recommendation is to simply use a range in that string sufficient to cover looking over the most number of rows to cover the longest likely dataset, and no more.

What do you mean?

I meant that I started a new query to get the MAX,MIN and Last Filled Row No
http://www.excelforum.com/excel-work...me-format.html

but this one is already SOLVED...

Thanks