Vlookup (but select range based on values)

Dear Friends,

i have a excel file which i have to perform a complex vlookup in sheet1

sheet 1 has 2000 rows and 9 coloumns, i need to vlookup (also match column) and fill the values in sheet2

the problem is the values i am performing vlookup and match have multiple presence, but their intersection is only one value (which i want vlookup to return).

i am attaching the file (have a look at both sheets).

kindly help.

regards
atul

Try

in B2

=IFERROR(INDEX(Sheet1!$D$5:$I$86,MATCH(Sheet2!B$1,Sheet1!$C$5:$C$86,0),MATCH(Sheet2!$A2,Sheet1!$D$4:$I$4,0)),"")

Copy across and down

Unfortunately none of your sample (column A) match so I changed one reference to test it.

hey, this returns a black cell. and column A in sheet2 matches in 4 rows in Sheet1.

Hi learnall18,

The problem you've got is that your data contains multiple data sets. You would be best cleaning it up and then the solution should be a fairly straightforward INDEX/MATCH formula.

Regards,

Snook

I could not find a simple solution to your search

Therefore I created a database with a unique key for Item ID and Location with a macro
Then, the vlookup() was simple as shown.

thanks for reply snook, yes there are multiple data sets, but there is consistency so i feel this formula is achievable.

how can i do that??

Here is the file with the macro included...

It is not letting me load the file so I include the macro
A tab under the name 19.05.2016 needs to be created

Please Login or Register  to view this content.

I didn't realise the data was in multiple tables: it needs all locations down column A and corresponding "codes" across columns then the INDEX MATCH of the form I have will work with appropriate range changes.

Sample file attached showing preferred format. Not All data has been reformatted - rows 301 onwards need to copied to columns as per other data.

thanks, but is it possible to achieve it using a formula?

thanks john, can we use SETOFF and achieve the formula without formatting?

Data fully formatted.

You will need VBA to do this normally (I just did copy/paste)

thanks rcm, macro seems doing it, can u also help me with a link for basic macro tutorial? does this macro also work on google sheet? is it possible to achieve a formula to do this?

thanks john! will try with vba..do u think its possible to achieve a formula to do this? if no, then will go with vba, as i am not good with vba i generally avoid it.

I think it would be very difficult to reformat using formulae: I don't know it would be done.

the macro below will organize the data in a matrix form. If you care to try it create a tab named "columns"
regarding the post #15 I would look it up but googleing "VBA tutorial" can be a start

Please Login or Register  to view this content.

Macro to reformat code

Please Login or Register  to view this content.

"Input" sheet to "OuPut" sheet.

thanks a lot john, this is doing the trick, however since there is inconsistency in input files. i tried converting my pdf from nitro and it gave me all tables in seperate sheets, now i tried index/match formula and used iferror function to include all sheets in search, but the formula is very very long, can you tell me how to make a formula which includes all sheets?. attaching new file. thanks inadvance

I don't think you can create a formula which looks at all sheets: hence the need of the (a) macro to convert (copy) all the data to one sheet.

Such a formula would have (or have the ability) to look at all sheets ... IF(INDEX(Sheet! ........,IF(........))))): totally impractical in my view.

i just learned recording a macro, i dont know vba...but recording a macro seems easy....is it reliable ??

and i was browsing the forum, there is a trick by naming all sheet names as range and then do index/match, however i am unable to construct the right formula, can that named range work??

this is an alternative.
1. create a tab named "Master"
2. place it at the end
3. run the macro
4. Master should have the data from all pages in one tab

Please Login or Register  to view this content.

Go with the macro solution provided by "rcm".

ARRAY formula in B2 of Sheet2, then drag across.

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

also, thank you john and rcm for the effort!! :-) great full to you

Very impressed by kvsrinivasamurthy's formula: yet to "decipher" it!

Thank you for the feedback and rep: much appreciated.

For your new excel file with more number of worksheets.
ARRAY formula in B2 of Sheet "Final", then drag across

Please Login or Register  to view this content.

thank you. it works great :-)

You are welcome.