LOOKUP vs IF (Need help)

[bassinator]

Good day to all, I’ve seen this forum a lot when I search for excel tips and decided to register because I got a good question regarding the Lookup IF function.

I do a lot of repeated work and I’m trying to simplify most of it.

Normally I use this Lookup in order to copy data from another sheet or spreadsheet that has a unique value (false) in order to obtain the information.

Example:

Sheet 1 – Spreadsheet
• A=Reference Number
• B=Estimated Cost
• C=Text of subject

A = 123 B= $20.00 C= Salaries

Now what I’m trying to do is

Sheet 2 – Spreadsheet (Same layout but different file)
• A=Reference Number
• B=Estimated Cost
• C=Text of subject

Now to get the data of the C to my new “updated report” with matching the Reference number I usually do something like this, =VLOOKUP(A2, Sheet1!A:C, 3, FALSE)

What I’m trying to mix with the IF function, I want it to automatically search IF for “Reference Number” then get the data below” and start the v look up from the other sheet.

Fake example cause it doesn’t work! But will give you an idea of what I’m trying to do.

=IF(1:1, 'Reference Number' = +1, false),(VLOOKUP(A2, Sheet1!A:C, 3, FALSE)

Maybe there is a better way?

Thank for your help in reviewing this post,

Bass

[mahju]

HI
I can not understand
Please upload a sample file showing your input and expected results.

Thanks

[bassinator]

Here is the example I did for this post, you will notice that there is 2 sheets in that data, I sometimes work with sheets or even spreadsheets.

I know it's possible to do via Macro but I'm trying to look for an alternative without using macro.

Let me know if you have any questions,

Thank you for your help,

[Fotis1991]

I think your request is not clear.

As i see your data in your example a simple VLOOKUP or an INDEX-MATCH, works ok...

[bassinator]

Hi Fotis,

I know a Vlookup works fine,

Depending on which reports i receive, sometimes, the reference is on line 10 so I must adjust the V look up value each time. I thought there was a way of making it "Look for "if title = reference", if True, look for the reference data below and then the match would be transferred from sheets. That way, I wouldn’t need to even think which row or column it is.

Maybe what I’m asking is too complex or my example is too small, because the reports I receive are bigger with a lot more data on it and the V Look up has helped a lot, but I thought, what IF I tell the V look up to search for that Title in the tab range of the report and now search for the reference and give me the value i want

Let me know, it not possible, I appreciate your help.

[Fotis1991]

Do you want to search in multiple sheets?

Yes. Your sample is too small, because in your example, something like this works OK.

=IFERROR(INDEX(Sheet1!$C$2:$C$5000,MATCH(Sheet2!B2,Sheet1!$B$2:$B$5000,0)),"")